Module  : Sphinx Search
Version : 1.0.0
Author  : Thomas Seiffert, updated by Martijn van Maasakkers <martijn@vanmaasakkers.net>

Module for using sphinx as fulltext search-engine instead of phorum built-in search
sphinx can be downloaded and installed from http://sphinxsearch.com/

Installation instructions:
--------------------------

To get this to work you will need to install sphinx on your server.
This will not be possible in a shared hosting environment and no, there
is no workaround for that.

some caveats:
  - data is stored in both phorum_search-table (not needed here) and in the
    sphinx filesystem - this doubles the sizes needed for search even though
    the search-table is not needed. You can clear it out every now and then.
  - search-data is not updated in real-time - sphinx reindexes the data if the indexer
    is called for the index, which does a full reindexing of the sphinx index.
    There is a workaround available in sphinx http://sphinxsearch.com/doc.html#live-updates
    which works essentially with two indexes. A large one and smaller one with only the deltas
    to the full index and which can be reindexed therefore more often, but I don't use that method yet.
    Still, its not realtime.
    For me the full indexing of 350,000 messages took around 5 minutes.
    I'd run the full indexing around once a day with those times.


Before you can run a search through sphinx you will need to setup sphinx correctly and do at least one full
indexing of your phorum-data.
Try the searching on the command-line using search from the sphinx install first.
Also you will need to start searchd from sphinx so that the mod can connect to it for searching.

The relevant parts from my sphinx.conf:

You will need to change *AT LEAST* the database settings, i.e. username, password, database-name and maybe
the table name for the messages table used.
If you change the index names you will need to change them in the sphinx_search too.

ATTENTION: 

The delta indexes require MySQL-4.1 or higher because of the use of subqueries and they need a separate table 
for holding the last indexed ID, its structure is the following (install in phorum database):

CREATE TABLE sph_counter (
  counter_id int(11) unsigned NOT NULL default '0',
  `type` enum('author','message') NOT NULL default 'message',
  max_doc_id int(11) NOT NULL,
  PRIMARY KEY  (counter_id,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



Example (working) sphinx config. Make sure to AT LEAST change the database settings
-------------------------------------------------------------------------------------
source phorum5_base
{
        # data source type
        # for now, known types are 'mysql' and 'xmlpipe'
        # MUST be defined
        type                            = mysql

        # whether to strip HTML
        strip_html                      = 0

        # what HTML attributes to index if stripping HTML
        index_html_attrs        =

        #################################################################

        # some straightforward parameters for 'mysql' source type
        sql_host                = localhost
        sql_user                = username
        sql_pass                = password
        sql_db                  = database
        sql_port                = 3306    # optional, default is 3306


        # optional, default is empty
        sql_group_column        = forum_id
        sql_date_column         = datestamp


}

index phorum5_base
{

        # morphology
        # default is not to use any
        morphology              = none

        # stopwords file
        stopwords               =

        # minimum word length
        min_word_len            = 1

        # charset encoding type
        # known types are 'sbcs' (Single Byte CharSet) and 'utf-8'
        charset_type            = sbcs

        path                    = /usr/local/sphinx/var/data/foo

}

source phorum5_msg : phorum5_base
{
        sql_query_pre           = REPLACE INTO sph_counter SELECT 1, 'message', MAX(message_id) FROM phorum_messages WHERE status=2
        sql_query               = \
                SELECT message_id, forum_id, datestamp, author, subject, body \
                FROM phorum_messages \
                WHERE message_id<=( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 and type = 'message' ) and status=2

}
source phorum5_msg_delta : phorum5_base
{

        # main document fetch query
        # you can specify any number of fields
        #
        # mandatory
        sql_query               = \
                SELECT message_id, forum_id, datestamp, author, subject, body \
                FROM phorum_messages \
                WHERE message_id>( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 and type = 'message' ) and status=2

}

index phorum5_msg : phorum5_base
{
        # which document source to index
        source                  = phorum5_msg

        # this is path and index file name without extension
        # files <indexpath>.spi/spd/spr will be created by indexer
        path                    = /usr/local/sphinx/var/data/phorum5_msg

}

index phorum5_msg_d : phorum5_base
{
        # which document source to index
        source                  = phorum5_msg_delta

        # this is path and index file name without extension
        # files <indexpath>.spi/spd/spr will be created by indexer
        path                    = /usr/local/sphinx/var/data/phorum5_msg_d

}

source phorum5_author : phorum5_base
{
        sql_query_pre           = REPLACE INTO sph_counter SELECT 1, 'author', MAX(message_id) FROM phorum_messages WHERE status=2
        sql_query               = \
                SELECT message_id, forum_id, datestamp, author \
                FROM p50_000001_messages \
                WHERE message_id<=( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 and type = 'author' ) and status=2
}

source phorum5_author_delta : phorum5_base
{

        # main document fetch query
        # you can specify any number of fields
        #
        # mandatory
        sql_query               = \
                SELECT message_id, forum_id, datestamp, author \
                FROM phorum_messages \
                WHERE message_id>( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 and type = 'author' ) and status=2

}

index phorum5_author : phorum5_base
{
        source = phorum5_author
        path = /usr/local/sphinx/var/data/phorum5_author
}



index phorum5_author_delta : phorum5_base
{
        source = phorum5_author_delta
        path = /usr/local/sphinx/var/data/phorum5_author_delta
}



---------------------------------------------------------------------------------------------


